import time
import urllib.parse
import urllib.request
import numpy as np
import pandas as pd
import pymysql
import requests
from PyQt5 import QtCore, QtWidgets
from PyQt5.QtCore import Qt, QDateTime
from PyQt5.QtGui import QIcon
from PyQt5.QtWidgets import *
from bs4 import BeautifulSoup
from lxml import etree

from CompanyInformation import tableInfoModel


class Ui_MainWindow(QMainWindow):

    def __init__(self):
        super(QtWidgets.QMainWindow, self).__init__()
        self.setupUi(self)
        self.retranslateUi(self)

    # 设置界面的控件
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        # 运行界面的大小
        MainWindow.resize(750, 715)
        #设置窗口图标
        MainWindow.setWindowIcon(QIcon('./images/spider.png'))
        MainWindow.setStyleSheet("background-image:url(./images/blue1.png);")
        self.centralWidget = QtWidgets.QWidget(MainWindow)
        self.centralWidget.setObjectName("centralWidget")
        self.retranslateUi(MainWindow)

        self.tableWidget = QtWidgets.QTableWidget(self.centralWidget)
        # 表格显示框的大小(距左边的距离，距上面的距离，宽度，高度)
        self.tableWidget.setGeometry(QtCore.QRect(20, 160, 710, 535))
        self.tableWidget.setObjectName("tableWidget")
        #self.tableWidget.setStyleSheet("background-image:url(./images/blue5.png);")
        self.tableWidget.setColumnCount(0)
        self.tableWidget.setRowCount(0)
        self.tableWidget.setStyleSheet("selection-background-color:pink")
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.tableWidget.raise_()

        # 设置最小值和最大值：实现无日期范围搜索
        self.label1 = QtWidgets.QLabel(self.centralWidget)
        self.label1.setGeometry(100, 20, 80, 28)
        self.label1.setStyleSheet("background:transparent;")
        self.label1.setText('爬取时间:')
        self.QDatetimeEdit = QtWidgets.QDateTimeEdit(self.centralWidget)
        self.QDatetimeEdit.setGeometry(QtCore.QRect(180, 20, 190, 28))
        # 设置格式和默认值
        self.QDatetimeEdit.setDisplayFormat("yyyy-MM-dd HH:mm:ss")
        self.QDatetimeEdit.setCalendarPopup(True)

        self.label1 = QtWidgets.QLabel(self.centralWidget)
        self.label1.setGeometry(380, 20, 80, 28)
        self.label1.setText('至')
        self.label1.setStyleSheet("background:transparent;")
        self.QDatetimeEdit1 = QtWidgets.QDateTimeEdit(self.centralWidget)
        self.QDatetimeEdit1.setGeometry(QtCore.QRect(400, 20, 190, 28))
        self.QDatetimeEdit1.setDisplayFormat("yyyy-MM-dd HH:mm:ss")
        self.QDatetimeEdit1.setCalendarPopup(True)
        self.QDatetimeEdit1.setDateTime(QDateTime.currentDateTime())

        self.label1 = QtWidgets.QLabel(self.centralWidget)
        self.label1.setGeometry(100, 60, 80, 28)
        self.label1.setText('公司名称:')
        self.label1.setStyleSheet("background:transparent;")
        self.searchLab1 = QtWidgets.QLineEdit(self.centralWidget)
        self.searchLab1.setGeometry(QtCore.QRect(180, 60, 190, 28))
        self.searchLab1.setPlaceholderText('请输入需要查询的关键字')

        self.label1 = QtWidgets.QLabel(self.centralWidget)
        self.label1.setGeometry(380, 60, 80, 28)
        self.label1.setText('是否失信:')
        self.label1.setStyleSheet("background:transparent;")
        self.QCombox = QtWidgets.QComboBox(self.centralWidget)
        self.QCombox.setGeometry(450, 60, 140, 28)
        self.QCombox.addItem('')
        self.QCombox.addItem('是')
        self.QCombox.addItem('否')
        self.pushButton = QtWidgets.QPushButton(self.centralWidget)
        self.pushButton.setGeometry(QtCore.QRect(100, 100, 80, 30))
        self.pushButton.setObjectName("pushButton")
        self.pushButton.setText("导  入")
        self.pushButton.setStyleSheet("QPushButton{color:black}"                                
                               "QPushButton:hover{color:rgb(51,87,255)}"      
                               "QPushButton{background-color:rgb(102,204,255)}")
        self.pushButton_4 = QtWidgets.QPushButton(self.centralWidget)
        self.pushButton_4.setGeometry(QtCore.QRect(300, 100, 80, 30))
        self.pushButton_4.setObjectName("pushButton_4")
        self.pushButton_4.setText("爬  取")
        self.pushButton_4.setStyleSheet("QPushButton{color:black}"                  
                               "QPushButton:hover{color:rgb(51,87,255)}"          
                               "QPushButton{background-color:rgb(102,204,255)}")
        self.pushButton_5 = QtWidgets.QPushButton(self.centralWidget)
        self.pushButton_5.setGeometry(QtCore.QRect(500, 100, 80, 30))
        self.pushButton_5.setObjectName("pushButton_5")
        self.pushButton_5.setText("查  询")
        self.pushButton_5.setStyleSheet("QPushButton{color:black}"                    
                               "QPushButton:hover{color:rgb(51,87,255)}"                 
                               "QPushButton{background-color:rgb(102,204,255)}")
        MainWindow.setCentralWidget(self.centralWidget)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

        self.pushButton.clicked.connect(self.openfile)
        self.pushButton.clicked.connect(self.creat_table_show)
        self.pushButton_4.clicked.connect(self.pushButton_click4)
        self.pushButton_5.clicked.connect(self.pushButton_click5)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "企业信用"))

    # ’导入excel‘按钮
    def openfile(self):
        ###获取路径===================================================================
        openfile_name = QFileDialog.getOpenFileName(None, '选择文件', '',
                                                    "*.csv;;*.xsl;;All Files(*)")

        print(openfile_name)
        global path_openfile_name
        ###获取路径====================================================================
        path_openfile_name = openfile_name[0]

    def creat_table_show(self):
        global creat_table_show
        ###===========读取表格，转换表格，===========================================
        if len(path_openfile_name) > 0:
            input_table = pd.read_csv(path_openfile_name, encoding="gbk")
            print(input_table)
            input_table_rows = input_table.shape[0]
            print(input_table_rows)
            input_table_columns = input_table.shape[1]
            print(input_table_columns)
            ###===========读取表格，转换表格，============================================
            ###======================给tablewidget设置行列表头===========================
            self.tableWidget.setColumnCount(3)
            self.tableWidget.setRowCount(input_table_rows)
            self.tableWidget.setHorizontalHeaderLabels(['公司名称', '爬取时间', '是否失信'])
            self.tableWidget.setColumnWidth(0, 280)
            self.tableWidget.setColumnWidth(1, 250)
            self.tableWidget.setColumnWidth(2, 128)
            ###======================给tablewidget设置行列表头============================
            ###================遍历表格每个元素，同时添加到tablewidget中========================
            for i in range(input_table_rows):
                input_table_rows_values = input_table.iloc[[i]]
                input_table_rows_values_array = np.array(input_table_rows_values)
                input_table_rows_values_list = input_table_rows_values_array.tolist()[0]
                for j in range(input_table_columns):
                    input_table_items_list = input_table_rows_values_list[j]
                    ###==============将遍历的元素添加到tablewidget中并显示=======================
                    input_table_items0 = str(input_table_items_list)
                    newItem0 = QTableWidgetItem(input_table_items0)
                    newItem0.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
                    self.tableWidget.setItem(i, j, newItem0)
                    print(i, j)
                    input_table_items1 = time.strftime('%Y-%m-%d', time.localtime())
                    newItem1 = QTableWidgetItem(input_table_items1)
                    newItem1.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
                    self.tableWidget.setItem(i, j + 1, newItem1)
        ###================遍历表格每个元素，同时添加到tablewidget中========================
        else:
            self.centralWidget.show()

    # ’爬取‘按钮
    def pushButton_click4(self):
        cookies = 'jsid=https%3A%2F%2Fwww.tianyancha.com%2F%3Fjsid%3DSEM-BAIDU-PZ-SY-2021112-JRGW; ssuid=4279527930; TYCID=d66c9f60bb9b11ebaa0167e7b0b72860; undefined=d66c9f60bb9b11ebaa0167e7b0b72860; _ga=GA1.2.828184792.1621756358; creditGuide=1; bad_id658cce70-d9dc-11e9-96c6-833900356dc6=551f07e1-bbaf-11eb-9bbf-4b0d779ef7ed; _gid=GA1.2.1963969309.1622201393; bdHomeCount=2; CT_TYCID=a31d1063875940d5851f29f09e442f21; RTYCID=7c4fda1742074c93b1269442a033905e; tyc-user-phone=%255B%252218613838138%2522%252C%2522138%25200544%25205509%2522%255D; searchSessionId=1622258389.09386256; aliyungf_tc=df909348cf88cc41eb11e2d012708617b95ecd11a72714e8dc26432d10399b7c; csrfToken=u5YOk2FIVNvuixkUlddcDneq; bannerFlag=true; Hm_lvt_e92c8d65d92d534b0fc290df538b4758=1622249450,1622249500,1622249663,1622286937; tyc-user-info={%22state%22:%225%22%2C%22vipManager%22:%220%22%2C%22isExpired%22:%220%22%2C%22mobile%22:%2218613838138%22}; tyc-user-info-save-time=1622368719118; auth_token=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiIxODYxMzgzODEzOCIsImlhdCI6MTYyMjM2ODcyMCwiZXhwIjoxNjUzOTA0NzIwfQ.I-fzu1MH7IQBbdm3hZZTKi43wHVog0zyZ9v5V-iK8m2QRifjI_jE9M-8TZq8AkMQIiFFStkuE5NePEXD-ITzTw; relatedHumanSearchGraphId=3361640448; relatedHumanSearchGraphId.sig=BhUitQBLOyxCqJvP1LULLeGcQYLC60RUBQOvRLI9I4w; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2218613838138%22%2C%22first_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%7D; acw_tc=781bad1116223708691748515e18a8f1d5469f5aad4e2727bd91649c33b399; acw_sc__v2=60b36a35eef2f28d31fc9b2d3bade2770d021bf1; _gat_gtag_UA_123487620_1=1; Hm_lpvt_e92c8d65d92d534b0fc290df538b4758=1622370874; cloud_token=8f1bd31899814b38986a2489bfb336c6; cloud_utm=90b0382b0a454da688ad2c973df5f41c'
        cookie = {i.split("=")[0]: i.split("=")[1] for i in cookies.split(";")}
        headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36',
            # 'Cookie':'jsid=https%3A%2F%2Fwww.tianyancha.com%2F%3Fjsid%3DSEM-BAIDU-PZ-SY-2021112-JRGW; ssuid=4279527930; TYCID=d66c9f60bb9b11ebaa0167e7b0b72860; undefined=d66c9f60bb9b11ebaa0167e7b0b72860; _ga=GA1.2.828184792.1621756358; creditGuide=1; bad_id658cce70-d9dc-11e9-96c6-833900356dc6=551f07e1-bbaf-11eb-9bbf-4b0d779ef7ed; _gid=GA1.2.1963969309.1622201393; bdHomeCount=2; CT_TYCID=a31d1063875940d5851f29f09e442f21; RTYCID=7c4fda1742074c93b1269442a033905e; tyc-user-phone=%255B%252218613838138%2522%252C%2522138%25200544%25205509%2522%255D; searchSessionId=1622258389.09386256; aliyungf_tc=df909348cf88cc41eb11e2d012708617b95ecd11a72714e8dc26432d10399b7c; csrfToken=u5YOk2FIVNvuixkUlddcDneq; bannerFlag=true; Hm_lvt_e92c8d65d92d534b0fc290df538b4758=1622249450,1622249500,1622249663,1622286937; acw_tc=781bad4416223686993277637e109456cd92024a6e30df99332aa91ee7ceeb; acw_sc__v2=60b361bb14331e8f90c0199be1bba3f019e54559; tyc-user-info={%22state%22:%225%22%2C%22vipManager%22:%220%22%2C%22isExpired%22:%220%22%2C%22mobile%22:%2218613838138%22}; tyc-user-info-save-time=1622368719118; auth_token=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiIxODYxMzgzODEzOCIsImlhdCI6MTYyMjM2ODcyMCwiZXhwIjoxNjUzOTA0NzIwfQ.I-fzu1MH7IQBbdm3hZZTKi43wHVog0zyZ9v5V-iK8m2QRifjI_jE9M-8TZq8AkMQIiFFStkuE5NePEXD-ITzTw; relatedHumanSearchGraphId=3361640448; relatedHumanSearchGraphId.sig=BhUitQBLOyxCqJvP1LULLeGcQYLC60RUBQOvRLI9I4w; sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%2218613838138%22%2C%22first_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22%24device_id%22%3A%221799836236826d-0e9408ac382ea9-3f356b-1327104-179983623691e5%22%7D; Hm_lpvt_e92c8d65d92d534b0fc290df538b4758=1622369858'
        }

        def get_url_tree(url):
            # 对网站发起请求
            page_test = requests.get(url=url, headers=headers, cookies=cookie).text
            # 这里是将从互联网上获取的源码数据加载到该对象中
            tree = etree.HTML(page_test)
            return tree

        with open('Data/企业.txt', encoding='utf-8') as file:
            line_list = [k.strip() for k in file.readlines()]  # 用 strip()移除末尾的空格
        print(line_list)
        for word in line_list:
            # 先打开一个网址
            url = 'https://www.tianyancha.com/search?key=' + urllib.parse.quote(word)
            print(url)
            # 在获取第二个url
            tree = get_url_tree(url)
            # // *[ @ id = "search_company_0"] / div / div[3] / div[1] / a
            # result =  re.findall('<div.*?><a.*?href="(.*?)">.*?</a>.*?<div>', re.S)
            url_list = tree.xpath('// *[ @ id = "search_company_0"] / div / div[3] / div[1] / a/ @href')
            print(url_list)
            page_test = requests.get(url=url_list[0], headers=headers, cookies=cookie).text
            time.sleep(30)
            soup = BeautifulSoup(page_test, 'html.parser')  # 创建soup对象，获取html代码
            tree = get_url_tree(url_list[0])
            pingfen = soup.find('span', class_="sort-score-value").get_text()
            renyuanguimo = tree.xpath('// *[ @ id = "_container_baseInfo"] / table / tbody / tr[7] / td[6]/ text()')[0]
            yingyeqixian = \
                tree.xpath('// *[ @ id = "_container_baseInfo"] / table / tbody / tr[6] / td[2] / span/ text()')[0]
            id = url_list[0].split('//')[1]
            cid = id.split('/', 2)[2]
            # 从而获得最终的url
            urls = 'https://www.tianyancha.com/risk/detail?watchType=1&cid=' + cid + '&source=company'
            page_test = requests.get(url=urls, headers=headers, cookies=cookie).text
            soup = BeautifulSoup(page_test, 'html.parser')  # 创建soup对象，获取html代码
            fengxianshu = soup.find('span', class_="risk-count").get_text()
            shixinfou = soup.find('span', class_="normal-color").get_text()
            print("风险总数" + ":" + fengxianshu)
            print("是否进入失信名单" + ":" + shixinfou)
            print("评分:" + pingfen)
            print("人员规模:" + renyuanguimo)
            print('营业期限:' + yingyeqixian)
            #把数据库连接封装起来 （加上try catch）
            conn = pymysql.connect(host="127.0.0.1", user="root", password="243420", database="enterprise",
                                   charset="utf8")
            #conn1 = pymysql.connect(host="127.0.0.1", user="root", password="243420", database="enterprise",charset="utf8")
            cursor = conn.cursor()
            # 判断公司名称是否存在,已存在进行更新操作，否则执行插入操作
            sql = "select *  from company where company_name='"+word+"'"
            cursor.execute(sql)  # 执行sql语句
            results = cursor.fetchall()    # 获取查询的所有记录
            company_name = []
            for row in results:
                company_name.append(row[1])
            #获取当前时间
            crawling_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            if len(company_name) == 1:
                sql = "update company set last_crawling_time ='"+crawling_time+"',dishonor_flag = '"+shixinfou+"' where company_name = '"+company_name[0]+"'"
                cursor.execute(sql)  # 执行sql语句
                conn.commit()
            else:
                value = (word, crawling_time, shixinfou)
                sql = "INSERT INTO company(company_name,last_crawling_time,dishonor_flag)VALUES(%s,%s,%s)"
                cursor.execute(sql, value)  # 执行sql语句
                conn.commit()
            '''cursor1 = conn1.cursor()
            value1 = (cid, shijian, pingfen, renyuanguimo, yingyeqixian, shixinfou, fengxianshu, urls)
            sql1 = "INSERT INTO crawling_details(detail_id,crawling_time,score,personnel_size,business_term,dishonor_flag,total_risk,crawling_url)VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
            cursor1.execute(sql1, value1)  # 执行sql语句
            conn1.commit()'''
            print("插入成功")
            #cursor.close()   # 关闭连接
            #cursor1.close()  # 关闭连接
            time.sleep(30)
        print("结束·")

    def viewTable(self, id):
        self.tableInfoModel = tableInfoModel(id)
        self.tableInfoModel.show()

    # 在列表中添加查看详情的按钮
    def buttonForRow(self, id):
        widget = QWidget()
        viewBtn = QPushButton('查 看')
        viewBtn.setStyleSheet("QPushButton{color:black}"
                                       "QPushButton:hover{color:rgb(51,87,255)}"
                                       "QPushButton{background-color:rgb(102,204,255)}")
        viewBtn.clicked.connect(lambda: self.viewTable(id))
        hLayout = QHBoxLayout()
        hLayout.addWidget(viewBtn)
        hLayout.setContentsMargins(5, 2, 5, 2)
        widget.setLayout(hLayout)
        return widget

    # ’查询按钮‘
    def pushButton_click5(self):

        # 获取输入框中的信息
        name = self.searchLab1.text()
        time = self.QDatetimeEdit.text()
        time1 = self.QDatetimeEdit1.text()
        flag = self.QCombox.currentText()
        self.tableWidget.setColumnCount(4)
        self.tableWidget.setColumnWidth(0, 240)
        self.tableWidget.setColumnWidth(1, 200)
        self.tableWidget.setColumnWidth(2, 110)
        self.tableWidget.setColumnWidth(3, 138)
        self.tableWidget.setHorizontalHeaderLabels(['公司名称', '爬取时间', '是否失信', '查看详情'])
        self.tableWidget.horizontalHeader().setStyleSheet(
            "QHeaderView::section{background-color:rgb(155, 194, 230);font:11pt '宋体';color: black;};")
        self.tableWidget.verticalHeader().setStyleSheet("QHeaderView::section{background-color:rgb(155, 194, 230);font:11pt '宋体';color: black;};")
        conn = pymysql.connect(host="127.0.0.1", user="root", password="243420", database="enterprise",
                               charset="utf8")
        cursor = conn.cursor()
        if flag == '':
            sql = "select *  from company where company_name like '%" + name + "%'" + "and last_crawling_time > '" + time + "' and last_crawling_time <'" + time1 + "'"
        else:
            sql = "select *  from company where company_name like '%" + name + "%'" + "and last_crawling_time > '" + time + "' and last_crawling_time <'" + time1 + "'" + "and dishonor_flag='" + flag + "'"
        cursor.execute(sql)  # 执行sql语句
        results = cursor.fetchall()  # 获取查询的所有记录
        company_id = []
        company_name = []
        last_crawling_time = []
        dishonor_flag = []
        for row in results:
            company_id.append(row[0])
            company_name.append(row[1])
            last_crawling_time.append(row[2])
            dishonor_flag.append(row[3])
        # 设置表格的行数
        self.tableWidget.setRowCount(len(company_id))
        # 循环输出数据库中的公司名单
        for i in range(0, len(company_id)):
            input_table_items01 = str(company_name[i])
            newItem01 = QTableWidgetItem(input_table_items01)
            newItem01.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
            self.tableWidget.setItem(i, 0, newItem01)
            input_table_items02 = str(last_crawling_time[i])
            newItem02 = QTableWidgetItem(input_table_items02)
            newItem02.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
            self.tableWidget.setItem(i, 1, newItem02)
            input_table_items03 = str(dishonor_flag[i])
            newItem03 = QTableWidgetItem(input_table_items03)
            newItem03.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
            self.tableWidget.setItem(i, 2, newItem03)
            # 添加按钮
            self.tableWidget.setCellWidget(i, 3, self.buttonForRow(str(company_id[i])))


if __name__ == "__main__":
    import sys

    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())
